package com.zt.questionnaire.common.service;

import com.zt.questionnaire.common.bean.excel.ReadExcel;
import com.zt.questionnaire.common.msg.HErrorCode;
import com.zt.questionnaire.common.msg.HResultMsg;
import com.zt.questionnaire.common.utils.DESUtils;
import com.zt.questionnaire.common.utils.TimeUtils;
import com.zt.questionnaire.common.utils.sign.MD5;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import java.util.zip.GZIPInputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;

//@ConfigurationProperties(prefix = "excel")
@Slf4j
@Component
@Data
public class HReadExcelFather {

    /**
     * txt下载路径
     **/
    @Value("${excel.path}")
//	private String excelPath;
    private String path;

    @Value("${spring.profiles.active}")
    private String active;

    /**
     * des加密密钥
     ***/
    private static String key = "ztwl_social_0627";

    /**
     * 使用GBK编码可以避免压缩中文文件名乱码
     */
    private static final String CHINESE_CHARSET = "GBK";
    /**
     * 文件读取缓冲区大小
     */
    private static final int CACHE_SIZE = 1024;

    public String getPath() {
//        log.info("excel地址={}",path + "/" + active);
        return path + "/" + active;
    }


    public static String[] titleNames = null;

    public static String[][] values = null;


    /**
     * 组合txt文件名
     *
     * @param fileName  excel文件名，不带扩展名
     * @param sheetName
     * @return
     */
    private static String getTxtName(String fileName, String sheetName) {
        StringBuffer sb = new StringBuffer(fileName);
        sb.append("_").append(sheetName).append(".txt");
        return sb.toString();
    }

    //	@SuppressWarnings("deprecation")
    public static ReadExcel readExcel(Sheet sheet, int headIndex, int dataIndex) throws Exception {
        if (sheet == null) {
            throw new Exception("错误");
        }
        int cellNums = sheet.getRow(headIndex).getLastCellNum();
        titleNames = new String[cellNums];
        for (int i = 0; i < cellNums; i++) {
            try {
                titleNames[i] = sheet.getRow(headIndex).getCell(i).getStringCellValue().trim();
                if (titleNames[i] == null || "null".equals(titleNames[i])) {
                    throw new Exception(sheet.getSheetName() + "表头有空格占位，i=" + i + ",表头=" + titleNames[i]);
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }

        DecimalFormat df = new DecimalFormat("0.000");

        values = new String[sheet.getLastRowNum() - headIndex][cellNums];
        for (int j = dataIndex; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row == null) {
                continue;
            }

            for (int k = 0; k < cellNums; k++) {
                Cell cell = row.getCell(k);
                if (cell == null) {
                    continue;
                }
                CellType cellType = cell.getCellTypeEnum();
                String value = null;
                if (cellType == CellType.NUMERIC) {
                    double numericCellValue = cell.getNumericCellValue();
                    int n = (int) numericCellValue;
                    Object obj = null;
                    if (numericCellValue == n) {
                        // 说明是整数类型
                        obj = n;
                    } else {
                        // 四舍五入，保留3位小数
                        // obj = df.format(numericCellValue);
                        obj = numericCellValue;
                    }
                    value = String.valueOf(obj);
                } else if (cellType == CellType.STRING) {
                    value = String.valueOf(cell.getStringCellValue());
                } else if (cellType == CellType.BOOLEAN) {
                    value = String.valueOf(cell.getBooleanCellValue());
                } else if (cellType == CellType.FORMULA) {
                    value = String.valueOf(cell.getCellFormula());
                }
                values[j - dataIndex][k] = value;
            }
        }
        ReadExcel obj = new ReadExcel();
        obj.setTitleNames(titleNames);
        obj.setValues(values);
        return obj;
    }

    /**
     * 生成TXT文件并上传到腾讯云,上传到腾讯云的文件使用des加密
     *
     * @param c          null时，当值为空时用"null"占位，否则就根据c对象的属性类型，当是数字时用0占位
     * @param fileName   excel文件名
     * @param fileName   sheet名
     * @param titleNames
     * @param values
     * @return 返回腾讯云路径 例：excel/point.txt
     * @throws Exception
     */
    public <T> String uploadTxt(Class<T> c, String fileName, String sheetName, String[] titleNames, String[][] values) throws Exception {
        String fileStr = "./file/tmp.txt";
        File file = createTxt(c, fileStr, titleNames, values);
        String fileName1 = StringUtils.substringBeforeLast(fileName, ".xlsx");
        String name = getTxtName(fileName1, sheetName);
        byte[] byt = desTxt(file);
//		log.info( this.getPath());
        log.info("======================excel表上传腾讯云filename={},sheetName={}", fileName1, sheetName);
//        String path1 = cosService.uploadDocTxt(byt, this.getPath(), name, true);
        String path1 = null;
        return path1;
    }

    /**
     * 生成TXT文件并上传到腾讯云,上传到腾讯云的文件使用des加密( 当值为null时，当值为空时用"null"占位)
     *
     * @param fileName   excel文件名
     * @param fileName   sheet名
     * @param titleNames
     * @param values
     * @return 返回腾讯云路径 例：excel/point.txt
     * @throws Exception
     */
    public String uploadTxt(String fileName, String sheetName, String[] titleNames, String[][] values) throws Exception {
        return uploadTxt(null, fileName, sheetName, titleNames, values);
    }


    public static <T> void addAttr(T obj, String name, String value) throws Exception {
        if (value == null || "".equals(value)) {
            return;
        }
        Field field = obj.getClass().getDeclaredField(name);
        field.setAccessible(true);

        if (field.getType().equals(Integer.TYPE) || field.getType().equals(Integer.class)) {
            field.set(obj, Integer.parseInt(value));
        } else if (field.getType().equals(Double.TYPE) || field.getType().equals(Double.class)) {
            field.set(obj, Double.parseDouble(value));
        } else if (field.getType().equals(Long.TYPE) || field.getType().equals(Long.class)) {
            field.set(obj, Long.parseLong(value));
        } else if (field.getType().equals(Float.TYPE) || field.getType().equals(Float.class)) {
            field.set(obj, Float.parseFloat(value));
        } else if (field.getType().equals(String.class)) {
            field.set(obj, value);
        }
    }

    /**
     * 当value是空的时候，如果是字符串类型返回"null"，否则返回0
     *
     * @param obj
     * @param name
     * @param value
     * @return
     * @throws Exception
     */
    private static <T> String handleValue(T obj, String name, String value) throws Exception {
        if (value != null && !"".equals(value)) {
            return value.replace("\n", "");
        }

        if (obj == null) {
            return "null";
        }

        Field field = obj.getClass().getDeclaredField(name);
        field.setAccessible(true);

        if (field.getType().equals(String.class)) {
            return "null";
        } else {
            return "0";
        }

    }

    public static <T> List<T> load(Class<T> c, String[] titleNames, String[][] values) throws Exception {
        List<T> list = new ArrayList<>();
        // 行数
        int row = values.length;
        // 列数
        int column = titleNames.length;
        for (int i = 0; i < row; i++) {
            T obj = c.newInstance();
            for (int k = 0; k < column; k++) {
                String name = titleNames[k];
                String value = values[i][k];
                if (value != null) {
                    value = value.trim();
                }
                addAttr(obj, name, value);
            }
            list.add(obj);
        }
        return list;
    }


    /**
     * 文件加密，加密后转成十六进制字符串后转byte
     *
     * @param file
     * @return
     * @throws Exception
     */
    private static byte[] desTxt(File file) throws Exception {
        InputStream is = null;
        byte[] byt = null;
        String toHexStr = null;
        try {
            is = new FileInputStream(file);
            int length = is.available();
            byt = new byte[length];
            is.read(byt);

            byte[] byt1 = DESUtils.encryptByte(byt, key.substring(0, 8));
            toHexStr = MD5.byteArrayToHexString(byt1);
        } finally {
            if (is != null) {
                is.close();
            }
        }
        return toHexStr.getBytes("utf-8");
    }

    public static void main(String[] args) throws Exception {

//		String st = "290C2C13636E42262A46EBE978289A3E45C0C17B48DE1495C7420DD903EE1DD7FBA38858DE1C9D07700DA7B92111EB58F0C80B35BF85B10FB4CC25DA487B149F4278E934F6AE17573044811E84F6D9830D873653E333267075C1B12BD83EF96D7D3DA984A48DA82E6C2CE835AF91A0C689E70B015E89D846B3B3AA1699E8BD6BBC36CFBDDEFFA13B0D873653E3332670E91D9743087392D67D3DA984A48DA82E61968EBCDE982417F029CA415789EC6F56C6914FE8811E00FC6DDB1447F0DA12DFE5AE1381F8BEF8A20CA35CF6CF75B03D4B0A29040FF896E2E0ABD42EAF6161CCD9E6F08D52DC0E56C6914FE8811E00D2442AC5AE11821332F9BCFC396C5E82BAA39023E5A873C6BA98E64C0C741C8D7CD9C86489B1E3F494906CC0D2726952213CDD9C281FA4735808FE43534CD37551C5A6478691BAD324739BE1A6D205E5BAE57AC857245178A6A2004D8C42E43C16DDFA70508F7B6BA8A2FCCF7E2DF036943F4ED4F00074EAEB3C7D34DB556D5E7D3DA984A48DA82EEC3DAD034CE31BDE19C3CA4EF3DB91FD3D91560277B71D29F12DE84DF6BC1F54954A8A2647DC2635A8A2FCCF7E2DF036A876A57EFBF3E79934DBBEA52BF5505C7D3DA984A48DA82E7D4DE0C1CE12BEA9F42B35689E29AC90BAA39023E5A873C6A164C38E60A068BAD3D9C3CABD006A8208096895709F7DA05F59227F5F";
//		String st = "4557a527c291e10e8cf192b8bf7b4fc6023877bdd4338b47434aabed8f31e2dd6e6d6647009caadcb79052109c0dca2d0ecbb1c14bd8fc5243ac071d6e610611ba7adbc7dca5f4f650bbffa5a7e1dfa1b67d26d185ff979b4556fe1343d855461e97d51b9b1ac3a4d7ce204a359f01ebed9e94cab589ec2439cebc6b9afe4b26565c0f90505d858163cffbf0ab4263905716f0d0f80badfed4c7a951cf595826c2031d89d345954f8c24710399e54605cfed29da19de6f6f792e3ad24978247b9be8b6b9d890b32f3d66d41d491bd1715f487ea058e8bd4edc8e26bb355efcccb753d8c1683a9235c4278a1e3fb4892811547f79da69c6568307b6b8952da0156d24f5b200ac2ec07b9114391bf250ead448c58b991ffef197cfa5ff80d0aa85a0cfdc97351505a4cf16f18ee4f7b53ce908aa75929c7ea89155666f5a66199b84748ec5ad6cb599815b15e4eac138065a4884a8388968a3647ae53f8adab11e6988b58661507bacb522e93e7287e7e2c29360cb69c207b2e1fb704d2f54a7e83c6a5bf3a27820dd3d1cc957fe3c0c816988b58661507bac7512991d542bdeb76be4a5a5de614d41815b15e4eac138069a3a44eb2622fe02ac58e4d64b229b162c9c69bba2802851a8f61991aef476d3943d20c430e0ac3e271c0ee0184e2995"

        String st = "34e6f9b3986ede9e37783826338a6ad89628a24870911099390c7914826475e914411dd32c0d63f96e82a8509a35a909e329dc8a8a88d75cfb396db8d8a1c3967bdef42c642fc97fca33de11b283eb2c635ee34096a1fe1a604466fdd22754da4bfe271909bc99b62f30e92e9214135b01bb9765850af1bb95348ecee1e4d2784934522c98221e83a7c83bfb9e0bd5f28a5556d75f1dd4f1717083ede6ec005e82ceb984af11fbf1d4a69d8e2e1cf7391a037732fff63c01c81d01af385869f267dd0a4f2a0305e412329be478c27b05d0129a9042ae228bf37862c9498317cd332f2871c8344f7e272f5f4bc202d1a3f4066f2d64ff56d40b786b547aa13592d041cbdf50e8362fbc96b208eccafbf87cd7010c6ec7cb2680abb3eefa395a517cd7010c6ec7cb266a6d3e93d664b07cd041cbdf50e8362fcc7096dc354ab0e64fa8ea8cd8074313bf6fedf49794b84e8be2b3940e2f13b90dbdcdc9796f515d264728fc1ba56f4e53a369962dc7e9940b786b547aa1359207b5cc55991c6206787fe231f169159786c5a8c3fea8e8eb9a3e3e9c68237cba4c8a57023d0c22c11a037732fff63c01f31391dfcb9795335bc0c895bc4bf5da4c7a2a2f5fc8f80e31c14f45ee3f5e0c650db4545e0d33f107b5cc55991c6206196469564d3011cdb551ae235a8d486cde3b429b316b40592782c607cdddeb96c998862a35b0cbc7863302ca7ecd52186b7ffd00be1e03de80abb3eefa395a516837241ba5ce03611e23cd9303faa3e0270d1b7d79ff33ff7d5e829fe15f6390331d917b2c0cfe2dd0129a9042ae228b0bcf10498b0ab9663c58adcba5dded34c1fb9aae3270a79817e6bf1e3b747f36a49ebac5624c8f71560c1c566257df7265b749ba6d197390a49ebac5624c8f718b6084e86c7fb374520f4d9ce1c960a7726cf89672017be91fdb3b61421476d3c8e6970093927f0b51e05c077d9e05b15d848ba9f17894148d3bb39e7eb816c192b0971d03439bfab0513095c38b1d0a87bcd387a5f1da1cdf81ec1906a2b23fb0513095c38b1d0ada5ca3cafd48b9c2f6a97d19c2fa5dd6c4aa185bbe589851ecdfc85874313134a04ba411cb654fe8c4aa185bbe5898517fa4bb68f30a3c1403e339429705bbbca9abdaaa15aa3bcb604f3042d2b8ef70a49ebac5624c8f712400fc7369222e12997e03060d48a675783c22e92dc429a032787d841b4df91fa49ebac5624c8f71560c1c566257df7265b749ba6d197390a49ebac5624c8f718b6084e86c7fb374cc1f2f1040769dba69fd765dfe015358604f3042d2b8ef70a49ebac5624c8f712400fc7369222e1277adc9bafdc99e5a";
        String str = DESUtils.decrypt1(st, key.substring(0, 8));
        File file = new File("E:/version.txt");
        FileUtils.writeByteArrayToFile(file, str.getBytes("utf-8"));
        log.info(str);

        jiaMi("E:/tmp.txt", "E:/jiaMi.txt");
        jiaMi("E:/AudioChildVersion.txt", "E:/jiaMi1.txt");
    }

    /**
     * 加密txt原始文件，临时使用
     *
     * @param fromFile
     * @throws Exception
     */
    public static void jiaMi(String fromFile, String toFile) throws Exception {
        File file = new File(fromFile);
        byte[] jiaMi = desTxt(file);
        String s = new String(jiaMi, "utf-8");
        File jiaMiFile = new File(toFile);
        FileUtils.writeByteArrayToFile(jiaMiFile, s.getBytes(StandardCharsets.UTF_8));

    }

    public static void jiaMi1(String fromFile, String toFile) throws Exception {
        File file = new File(fromFile);
        byte[] jiaMi = desTxt(file);
        File jiaMiFile = new File(toFile);
        FileUtils.writeByteArrayToFile(jiaMiFile, jiaMi);

    }

    /**
     * 将Excel文件转换成txt
     *
     * @param titleNames
     * @param values
     * @return
     * @throws Exception
     */
    public static <T> byte[] getTxtByte(String[] titleNames, String[][] values) throws Exception {
        String fileStr = "./file/tmp.txt";
        File file = createTxt(null, fileStr, titleNames, values);
        return getBytes(file);
    }

    /**
     * 将Excel文件转换成txt
     *
     * @param c
     * @param titleNames
     * @param values
     * @return
     * @throws Exception
     */
    public static <T> byte[] getTxtByte(Class<T> c, String[] titleNames, String[][] values) throws Exception {
        String fileStr = "./file/tmp.txt";
        File file = createTxt(c, fileStr, titleNames, values);
        return getBytes(file);
    }

    /**
     * 创建txt文件
     *
     * @param fileStr
     * @param titleNames
     * @param values
     * @return
     * @throws Exception
     */
    public static <T> File createTxt(Class<T> c, String fileStr, String[] titleNames, String[][] values) throws Exception {
        // 获取系统的换行符，Unix系统里，每行结尾只有“<换行>”，即“\n”；Windows系统里面，每行结尾是“<换行><回
        // 车>”，即“\r\n”；Mac系统里，每行结尾是“<回车>”。
        String lineS = System.getProperty("line.separator");
        lineS = "LineEnd";
        File file = null;
        FileWriter fw = null;
        file = new File(fileStr);
        try {
            if (!file.exists()) {
                file.createNewFile();
            }
            fw = new FileWriter(file);
            int rowSize = values.length - 1;
            int cellSize = titleNames.length - 1;

            for (int i = 0; i <= cellSize; i++) {
                fw.write(titleNames[i] + "\t");
            }
            fw.write(lineS);
            T obj = null;
            if (c != null) {
                obj = c.newInstance();
            }
            for (int i = 0; i <= rowSize; i++) {
                boolean isContinue = false;
                for (int j = 0; j <= cellSize; j++) {
                    String name = titleNames[j];
                    String v = values[i][j];
                    if (j == 0 && (v == null || "".equals(v))) {
                        // 说明整行数据可能为null，可能是最后一行了，后面后空格的东西，即使不是最后一行，如果第一列为空，也丢的这条数据
                        isContinue = true;
                        break;
                    }
                    v = handleValue(obj, name, v);
                    fw.write(v);
                    if (j != cellSize) {
                        fw.write("\t");
                    }
                }
                if (!isContinue && i != rowSize) {
                    fw.write(lineS);
                }
                fw.flush();
            }
            return file;
        } finally {
            if (fw != null) {
                fw.close();
            }
        }
    }

    /**
     * 获取文件二进制流
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static byte[] getBytes(File file) throws IOException {

        InputStream in = null;
        BufferedInputStream buffer = null;
        DataInputStream dataIn = null;
        ByteArrayOutputStream bos = null;
        DataOutputStream dos = null;
        byte[] bArray = null;
        try {
            in = new FileInputStream(file);
            buffer = new BufferedInputStream(in);
            dataIn = new DataInputStream(buffer);
            bos = new ByteArrayOutputStream();
            dos = new DataOutputStream(bos);
            byte[] buf = new byte[1024];
            while (true) {
                int len = dataIn.read(buf);
                if (len < 0)
                    break;
                dos.write(buf, 0, len);
            }
            bArray = bos.toByteArray();

        } finally {

            if (in != null) {
                in.close();
            }
            if (dataIn != null) {
                dataIn.close();
            }
            if (buffer != null) {
                buffer.close();
            }
            if (bos != null) {
                bos.close();
            }
            if (dos != null) {
                dos.close();
            }
        }

        return bArray;
    }

    /**
     * 解压压缩包
     *
     * @param zipFilePath 压缩文件路径
     * @param destDir     解压目录
     */
    public static void unZip(String zipFilePath, String destDir) {
        ZipFile zipFile = null;
        try {
            BufferedInputStream bis = null;
            FileOutputStream fos = null;
            BufferedOutputStream bos = null;
            zipFile = new ZipFile(zipFilePath, Charset.forName(CHINESE_CHARSET));
            Enumeration<? extends ZipEntry> zipEntries = zipFile.entries();
            File file, parentFile;
            ZipEntry entry;
            byte[] cache = new byte[CACHE_SIZE];
            while (zipEntries.hasMoreElements()) {
                entry = (ZipEntry) zipEntries.nextElement();
                if (entry.isDirectory()) {
                    new File(destDir + entry.getName()).mkdirs();
                    continue;
                }
                bis = new BufferedInputStream(zipFile.getInputStream(entry));
                file = new File(destDir + entry.getName());
                parentFile = file.getParentFile();
                if (parentFile != null && (!parentFile.exists())) {
                    parentFile.mkdirs();
                }
                fos = new FileOutputStream(file);
                bos = new BufferedOutputStream(fos, CACHE_SIZE);
                int readIndex = 0;
                while ((readIndex = bis.read(cache, 0, CACHE_SIZE)) != -1) {
                    fos.write(cache, 0, readIndex);
                }
                bos.flush();
                bos.close();
                fos.close();
                bis.close();
            }
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                zipFile.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    public static void unGzByte(byte[] buff, File outFile) throws Exception {
        ByteArrayInputStream in = new ByteArrayInputStream(buff);
        FileOutputStream out = new FileOutputStream(outFile);
        GZIPInputStream gis = new GZIPInputStream(in);

        int count;
        byte data[] = new byte[1024];
        while ((count = gis.read(data, 0, 1024)) != -1) {
            out.write(data, 0, count);
        }
        gis.close();
        out.close();
        in.close();
    }

    /**
     * 校验excel
     *
     * @param fexcel
     * @return
     */
    public static String checkExcel(MultipartFile fexcel) throws IOException {
        if (fexcel.isEmpty()) {
            return HResultMsg.createMsgError(HErrorCode.PARAM_ERROR, "请上传excel文件");
        }
        String fileName = fexcel.getOriginalFilename();
        if (fileName == null) {
            return HResultMsg.createMsgError(HErrorCode.PARAM_ERROR, "请上传excel文件");
        }
        String ext = ".xlsx";
        if (!fileName.endsWith(ext)) {
            return HResultMsg.createMsgError(HErrorCode.PARAM_ERROR_1, fileName + "不是excel文件");
        }
        byte[] buff = fexcel.getBytes();
        if (buff.length == 0) {
            return HResultMsg.createMsgError(HErrorCode.FAIL, "表里没有数据");
        }

        return null;
    }

    /**
     * 保存上传文件为临时文件
     *
     * @param fileName
     * @param folderName 文件夹目录名
     * @param buff
     * @throws IOException
     */
    public static File saveTempFile(String fileName, String folderName, byte[] buff) throws IOException {
        File file = new File(folderName + fileName);
        File tempParentFile = file.getParentFile();
        if (!tempParentFile.exists()) {
            tempParentFile.mkdirs();
        }
        FileUtils.writeByteArrayToFile(file, buff);
        return file;
    }

    /**
     * 保存备份文件
     *
     * @param file
     * @return folderName 文件夹名
     * @throws IOException
     */
    public static File saveBackupFile(File file, String folderName) throws IOException {
        String backupName = folderName + file.getName() +
            TimeUtils.longToYearMonthDayHourMinSecon(TimeUtils.getCurrTime());
        File backupFile = new File(backupName);
        File backupParentFile = backupFile.getParentFile();
        if (!backupParentFile.exists()) {
            backupParentFile.mkdirs();
        }
        FileUtils.copyFile(file, backupFile);
        return backupFile;
    }

    /**
     * 根据类获取excel表sheet名，
     * 格式：
     * excel表名: test
     * excel表中sheet: t1
     * excel类名: TestT1Excel
     *
     * @return test_t1
     */
    public static String getExcelSheetName(Class<?> cls) {
        String simpleName = cls.getSimpleName();
        // _test_t1_excel
        String lowerName = simpleName.replaceAll("[A-Z]", "_$0").toLowerCase();
        return lowerName.substring(1)
            .replace("_excel", "");
    }
}
